﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using motelmanagement.common;
using System.Data;
using System.Data.OleDb;
namespace motelmanagement.dao
{
    class HireDetailDAO
    {
        SqlUtil sqlUtil = new SqlUtil();
        OleDbCommand cmd = null;
        private string strSql = null;
        public DataTable getALLHireDetail()
        {
            strSql = "SELECT Hire_detail.hire_detail_id, Hire_detail.cus_id, Customer.cus_name";
            strSql += ", Customer.cus_idnumber, Customer.cus_address, Customer.cus_phone";
            strSql += ", Customer.cus_sex, Customer.cus_country, Customer.cus_note";
            strSql += ", Hire_detail.day_start, Hire_detail.day_end_1, Hire_detail.day_end_2";
            strSql += ", Hire_detail.total_time,Hire_detail.over_time, Hire_detail.room_status_id";
            strSql += ", Rooms.rooms_name, Hire_detail.pay_offfer, Hire_detail.staff_id";
            strSql += ", Staff.staff_name, Hire_detail.extra_id,Extra_price.extra_name";
            strSql += " FROM Extra_price INNER JOIN (Staff INNER JOIN (Rooms_status INNER JOIN";
            strSql += "(Rooms INNER JOIN (Customer INNER JOIN Hire_detail ON Customer.cus_id = ";
            strSql += "Hire_detail.cus_id) ON Rooms.rooms_id = Hire_detail.rooms_id)";
            strSql += " ON (Hire_detail.room_status_id = Rooms_status.room_status_id) AND ";
            strSql += "(Rooms_status.room_status_id = Rooms.room_status_id))";
            strSql += " ON Staff.staff_id = Hire_detail.staff_id) ON Extra_price.extra_id = Hire_detail.extra_id";
            cmd = new OleDbCommand(strSql);
            sqlUtil.Load(cmd);
            return sqlUtil;
        }
        public DataTable getHireDetail(int hireDetailID)
        {
            strSql = "SELECT Hire_detail.hire_detail_id, Hire_detail.cus_id, Customer.cus_name";
            strSql += ", Customer.cus_idnumber, Customer.cus_address, Customer.cus_phone";
            strSql += ", Customer.cus_sex, Customer.cus_country, Customer.cus_note";
            strSql += ", Hire_detail.day_start, Hire_detail.day_end_1, Hire_detail.day_end_2";
            strSql += ", Hire_detail.total_time,Hire_detail.over_time, Hire_detail.room_status_id";
            strSql += ", Rooms.rooms_name, Hire_detail.pay_offfer, Hire_detail.staff_id";
            strSql += ", Staff.staff_name, Hire_detail.extra_id,Extra_price.extra_name";
            strSql += " FROM Extra_price INNER JOIN (Staff INNER JOIN (Rooms_status INNER JOIN";
            strSql += "(Rooms INNER JOIN (Customer INNER JOIN Hire_detail ON Customer.cus_id = ";
            strSql += "Hire_detail.cus_id) ON Rooms.rooms_id = Hire_detail.rooms_id)";
            strSql += " ON (Hire_detail.room_status_id = Rooms_status.room_status_id) AND ";
            strSql += "(Rooms_status.room_status_id = Rooms.room_status_id))";
            strSql += " ON Staff.staff_id = Hire_detail.staff_id) ON Extra_price.extra_id = Hire_detail.extra_id";
            strSql += " Where hire_detail_id=@id";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("id", SqlDbType.Int).Value = hireDetailID;
            sqlUtil.Load(cmd);
            return sqlUtil;
        }
        public void insertHireDetail(int cusID, int roomID, string dayStart,
            string dayEnd1, string dayEnd2, string totalTime, string overTime,
            int roomStatusID, int payOffer, int totalPrice, int staffID, int extraID)
        {

            strSql = "Insert Into Hire_detail(cus_id,rooms_id,day_start,day_end_1,day_end_2";
            strSql += ",total_time,over_time,room_status_id,pay_offfer,total_price,staff_id,extra_id)";
            strSql += " Values(@cusid,@roomid,@start,@end1,@end2,@totaltime,@overtime";
            strSql += ",@roomttid,@pay,@price,@staff,@extra)";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("cusid", SqlDbType.Int).Value = cusID;
            cmd.Parameters.Add("roomid", SqlDbType.Int).Value = roomID;
            cmd.Parameters.Add("start", SqlDbType.Text).Value = dayStart;
            cmd.Parameters.Add("end1", SqlDbType.Text).Value = dayEnd1;
            cmd.Parameters.Add("end2", SqlDbType.Text).Value = dayEnd2;
            cmd.Parameters.Add("totaltime", SqlDbType.Text).Value = totalTime;
            cmd.Parameters.Add("overtime", SqlDbType.Text).Value = overTime;
            cmd.Parameters.Add("roomttid", SqlDbType.Int).Value = roomStatusID;
            cmd.Parameters.Add("pay", SqlDbType.Int).Value = payOffer;
            cmd.Parameters.Add("price", SqlDbType.Int).Value = totalPrice;
            cmd.Parameters.Add("staff", SqlDbType.Int).Value = staffID;
            cmd.Parameters.Add("extra", SqlDbType.Int).Value = extraID;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
        public void updateHireDetail(int hireID, int cusID, int roomID, string dayStart,
            string dayEnd1, string dayEnd2, string totalTime, string overTime,
            int roomStatusID, int payOffer, int totalPrice, int staffID, int extraID)
        {

            strSql = "Update Hire_detail SET cus_id=@cusid,rooms_id=@roomid";
            strSql += ",day_start=@start,day_end_1=@end1,day_end_2=@end2";
            strSql += ",total_time=@totaltime,over_time=@overtime,room_status_id=@roomttid";
            strSql += ",pay_offfer=@pay,total_price=@price,staff_id=@staff,extra_id=@extra";
            strSql += " Where hire_detail_id=@hireid";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("cusid", SqlDbType.Int).Value = cusID;
            cmd.Parameters.Add("roomid", SqlDbType.Int).Value = roomID;
            cmd.Parameters.Add("start", SqlDbType.Text).Value = dayStart;
            cmd.Parameters.Add("end1", SqlDbType.Text).Value = dayEnd1;
            cmd.Parameters.Add("end2", SqlDbType.Text).Value = dayEnd2;
            cmd.Parameters.Add("totaltime", SqlDbType.Text).Value = totalTime;
            cmd.Parameters.Add("overtime", SqlDbType.Text).Value = overTime;
            cmd.Parameters.Add("roomttid", SqlDbType.Int).Value = roomStatusID;
            cmd.Parameters.Add("pay", SqlDbType.Int).Value = payOffer;
            cmd.Parameters.Add("price", SqlDbType.Int).Value = totalPrice;
            cmd.Parameters.Add("staff", SqlDbType.Int).Value = staffID;
            cmd.Parameters.Add("extra", SqlDbType.Int).Value = extraID;
            cmd.Parameters.Add("hireid", SqlDbType.Int).Value = hireID;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
        public void deleteHireDetail(int hireID)
        {
            cmd = new OleDbCommand("Delete From Hire_detail Where hire_detail_id=@id");
            cmd.Parameters.Add("id",SqlDbType.Int).Value=hireID;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
    }
}
